SQL Joins

Joining datasets is one of the most fundamental operations in relational data processing. Spark DataFrames support all standard SQL join types, as well as highly optimized streaming-oriented joins.

graph TD
    subgraph StandardJoins["Standard Relational Joins"]
        direction LR
        J1["Inner Join<br>(Matches on both)"]
        J2["Outer Joins<br>(Matches + Null padded mismatches)"]
    end
    subgraph FilteringJoins["Filtering Joins (No Right Table Columns)"]
        direction LR
        J3["Left Semi Join<br>(Returns matching left records)"]
        J4["Left Anti Join<br>(Returns mismatched left records)"]
    end
    style StandardJoins fill:#eff6ff,stroke:#2563eb,stroke-width:2px;
    style FilteringJoins fill:#faf5ff,stroke:#9333ea,stroke-width:2px;

Join Types Supported in Spark

To execute a join, call the join() method on your left DataFrame: left_df.join(right_df, on_expression, join_type)

PySpark Code Example: Executing Standard and Advanced Joins

Here is a complete, copy-paste-ready script showing how to perform joins using single keys, multiple keys, and advanced Semi/Anti joins:

from pyspark.sql import SparkSession
from pyspark.sql.functions import col

# 1. Setup Spark
spark = SparkSession.builder \
    .appName("SQL Joins") \
    .master("local[*]") \
    .getOrCreate()

# 2. Sample Datasets
# Left: Employees
employees_data = [
    (1, "Alice", "Dept_A"),
    (2, "Bob", "Dept_B"),
    (3, "Charlie", "Dept_C"),
    (4, "David", "Dept_X")
]
employees_df = spark.createDataFrame(employees_data, ["emp_id", "name", "dept_id"])

# Right: Departments
departments_data = [
    ("Dept_A", "Engineering"),
    ("Dept_B", "Marketing"),
    ("Dept_C", "Sales"),
    ("Dept_D", "Finance")
]
departments_df = spark.createDataFrame(departments_data, ["dept_id", "dept_name"])

# 3. Inner Join (David is excluded; Dept D is excluded)
inner_df = employees_df.join(departments_df, "dept_id", "inner")
inner_df.show()

# 4. Left Outer Join (David is included with dept name as null)
left_df = employees_df.join(departments_df, "dept_id", "left")
left_df.show()

# 5. Left Semi Join (Filters employees who belong to a valid department)
semi_df = employees_df.join(departments_df, "dept_id", "left_semi")
semi_df.show()

# 6. Left Anti Join (Finds employees belonging to invalid/missing departments)
anti_df = employees_df.join(departments_df, "dept_id", "left_anti")
anti_df.show()